package com.eling.elcms.cater.dao.hibernate;


import com.eling.elcms.cater.dao.TravelAccountFlowDao;
import com.eling.elcms.cater.model.TravelAccountFlow;
import com.eling.elcms.cater.model.view.TravelAccountFlowView;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class TravelAccountFlowDaoHibernate extends GenericDaoHibernate<TravelAccountFlow, Long> implements TravelAccountFlowDao{
	
	public TravelAccountFlowDaoHibernate() {
		super(TravelAccountFlow.class);
	}

	@Override
	public void saveAttendant(TravelAccountFlow travelAccountFlow) {
		StringBuilder sql = new StringBuilder();
		
		sql.append(" INSERT INTO `cate_travelaccountflow` ( ");
		sql.append(" `balance`,`operateDate`,`version`,`pkUser`, ");
		sql.append(" `pkServicePoint`,`pkTravelAccount`,`rechargeType`) ");
		sql.append(" VALUES ('"+travelAccountFlow.getBalance()+"','"+travelAccountFlow.getOperateDate()+"', ");
		sql.append(" '"+travelAccountFlow.getVersion()+"','"+travelAccountFlow.getOperator().getPkUser()+"', ");
		sql.append(" '"+travelAccountFlow.getServicePoint().getPkServicePoint()+"','"+travelAccountFlow.getTravelAccount().getPkTravelAccount()+"', ");
		sql.append(" '"+travelAccountFlow.getRechargeType()+"',) ");
		
		getSession().createNativeQuery(sql.toString());
		
	}

	
	@Override
	public List<TravelAccountFlowView> employeeCount(Date operateDate, Date operateDateEnd) {
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT per. NAME,per.idNumber,SUM(tf.money),per.sex ");
		sql.append(" FROM cate_travelaccountflow tf ");
		sql.append(" INNER JOIN cate_travelaccount ta ON ta.pkTravelAccount = tf.pkTravelAccount ");
		sql.append(" INNER JOIN om_attendant att ON att.pkAttendant = ta.pkAttendance  ");
		sql.append(" INNER JOIN bd_personalinfo per ON per.pkPersonalInfo = att.pkPersonalInfo  ");
		sql.append(" WHERE tf.operateDate>='"+operateDate+"' AND tf.operateDate <='"+operateDateEnd+"' ");
		
		@SuppressWarnings("unchecked")
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("operateDate", operateDate)
				.setParameter("operateDateEnd", operateDateEnd).list();
		
		List<TravelAccountFlowView> flowViewsList = new ArrayList<>();
		
		for(Object[] objects:objList){
			if (objects[0] != null && objects[1] != null && !"".equals(objects[1])) {
				
				TravelAccountFlowView flowView  = new  TravelAccountFlowView();
				
				flowView.setPersonName(objects[0]==null?" ":objects[0].toString());
				flowView.setIdNumber(objects[1]==null?" ":objects[1].toString());
				flowView.setMoney(objects[2]==null?0.00:new Double( objects[2].toString()));
				flowView.setSex(objects[3]==null?" ":objects[3].toString());
				
				flowViewsList.add(flowView);
			}
		}
		return flowViewsList;
	}
}
